package Database;

import java.sql.*;
import java.util.ArrayList;

import Model.Staff;

//import Database.DBConnection;

public class StaffDB {

	public static ArrayList<Staff> getRoleTable() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT Credential_ID, Staff_Name, Role_Name FROM staff INNER JOIN credential ON credential.Credential_ID = staff.Staff_LoginID INNER JOIN role ON role.Role_ID = credential.Credential_Role ORDER BY Staff_Name";

		ArrayList<Staff> staffArr = new ArrayList<Staff>();
		Staff staff = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				staff = new Staff();
				staff.setId(rs.getString("Credential_ID"));
				staff.setName(rs.getString("Staff_Name"));
				staff.setRole(rs.getString("Role_Name"));

				staffArr.add(staff);
			}
		} catch (Exception e) {
			e.printStackTrace();
			staff = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return staffArr;
	}
	
	public static ArrayList<Staff> getApproverList() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT Credential_ID, Staff_Name, Staff_Email, Role_Name FROM staff INNER JOIN credential ON credential.Credential_ID = staff.Staff_LoginID INNER JOIN role ON role.Role_ID = credential.Credential_Role WHERE credential.Credential_Role = 3 ";

		ArrayList<Staff> staffArr = new ArrayList<Staff>();
		Staff staff = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				staff = new Staff();
				staff.setId(rs.getString("Credential_ID"));
				staff.setName(rs.getString("Staff_Name"));
				staff.setRole(rs.getString("Role_Name"));
				staff.setEmail(rs.getString("Staff_Email"));

				staffArr.add(staff);
			}
		} catch (Exception e) {
			e.printStackTrace();
			staff = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return staffArr;
	}

	public static String getStaffNamebyId(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT Staff_Name FROM staff WHERE Staff_LoginID = ?";
		String staffName = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				staffName = rs.getString("Staff_Name");
				System.out.println("SQL Statement in getStaffNamebyID : " + staffName);
			}
		} catch (Exception e) {
			e.printStackTrace();
			staffName = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return staffName;
	}
}
